import requests
import mysql.connector
from mysql.connector import Error

# 配置参数
# 阿里云OSS配置
ALIYUN_OSS_ACCESS_KEY_ID = 'LTAI5tKsHGypsVpazRgmbLRm'
ALIYUN_OSS_ACCESS_KEY_SECRET = 'YBeqokOdXpFh9Hk1Mdq9hBaob2JhUA'
ALIYUN_OSS_ENDPOINT = 'oss-cn-hangzhou.aliyuncs.com'
ALIYUN_OSS_BUCKET_NAME = 'knowage2025'
ALIYUN_OSS_BUCKET_DOMAIN = 'https://knowage2025.oss-cn-hangzhou.aliyuncs.com'
OSS_DOMAIN = ALIYUN_OSS_BUCKET_DOMAIN + '/'  # 替换为您的阿里云OSS地址

def fetch_data_from_api():
    """从API抓取数据"""
    url = 'http://gzh.zjxlx.org.cn/XtNet/Que/Lists'
    
    headers = {
        'Accept': 'application/json, text/javascript, */*; q=0.01',
        'Accept-Language': 'zh-CN,zh;q=0.9',
        'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
        'Origin': 'http://gzh.zjxlx.org.cn',
        'Proxy-Connection': 'keep-alive',
        'Referer': 'http://gzh.zjxlx.org.cn/XtNet/Que',
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36',
        'X-Requested-With': 'XMLHttpRequest'
    }
    
    cookies = {
        'ASP.NET_SessionId': 'v1qwnf5cgukzf1ggaamyjfry'
    }
    
    data = {
        'page': '1',
        'limit': '100',
        'queName': '',
        'classId': ''
    }
    
    try:
        response = requests.post(url, headers=headers, cookies=cookies, data=data, verify=False)
        response.raise_for_status()
        return response.json()
    except requests.exceptions.RequestException as e:
        print(f"请求API时出错: {e}")
        return None

def download_and_upload_image(image_url, object_name):
    """下载图片并上传到阿里云OSS"""
    import oss2
    import tempfile
    import os
    
    try:
        # 创建OSS认证对象
        auth = oss2.Auth(ALIYUN_OSS_ACCESS_KEY_ID, ALIYUN_OSS_ACCESS_KEY_SECRET)
        
        # 创建OSS客户端
        bucket = oss2.Bucket(auth, ALIYUN_OSS_ENDPOINT, ALIYUN_OSS_BUCKET_NAME)
        
        # 下载图片
        response = requests.get(image_url, stream=True, verify=False)
        response.raise_for_status()
        
        # 创建临时文件
        with tempfile.NamedTemporaryFile(delete=False, suffix='.jpg') as tmp_file:
            for chunk in response.iter_content(chunk_size=8192):
                tmp_file.write(chunk)
            tmp_file_path = tmp_file.name
        
        # 上传到OSS
        bucket.put_object_from_file(object_name, tmp_file_path)
        
        # 清理临时文件
        os.unlink(tmp_file_path)
        
        # 返回OSS访问地址
        return f"{ALIYUN_OSS_BUCKET_DOMAIN}/{object_name}"
        
    except Exception as e:
        print(f"处理图片 {image_url} 时出错: {e}")
        return None


def create_mysql_connection():
    """创建MySQL数据库连接"""
    try:
        connection = mysql.connector.connect(
            host='114.55.93.104',
            database='psychology_test',  # 请修改为您的数据库名
            user='psychology_test',           # 请修改为您的用户名
            password='123456'        # 请修改为您的密码
        )
        return connection
    except Error as e:
        print(f"连接MySQL数据库时出错: {e}")
        return None


def save_data_to_mysql(connection, data):
    """将数据保存到MySQL数据库"""
    if not data or 'data' not in data:
        print("没有有效数据需要保存")
        return
    
    insert_query = """
    INSERT INTO assessments (
        title, cover_image, description, subtitle, is_free, price, ai_report_enabled
    ) VALUES (
        %s, %s, %s, %s, %s, %s, %s
    )
    """
    
    update_query = """
    UPDATE assessments SET
        cover_image = %s,
        description = %s,
        subtitle = %s,
        is_free = %s,
        price = %s,
        ai_report_enabled = %s
    WHERE title = %s
    """
    
    check_query = "SELECT id FROM assessments WHERE title = %s"
    
    try:
        cursor = connection.cursor()
        
        # 根据API返回的数据结构处理数据
        for item in data.get('data', []):
            title = item.get('Name')
            if title == '心理健康评估':
                continue
            # 处理图片上传到阿里云OSS
            # original_image_url = 'http://gzh.zjxlx.org.cn' + item.get('Pic')
            # object_name = item.get('Pic').split('/')[-1]
            # cover_image = download_and_upload_image(original_image_url, object_name)
            
            # 如果图片处理失败，使用原始URL作为备选
            cover_image = ''
            description = item.get('Content')
            subtitle = item.get('Name')
            # 处理saleprice可能为None的情况
            saleprice = item.get('saleprice')
            is_free = 0 if saleprice and saleprice > 0 else 1
            price = saleprice if saleprice and saleprice > 0 else 0
            ai_report_enabled = 1
            
            # 检查title是否存在
            cursor.execute(check_query, (title,))
            existing_record = cursor.fetchone()
            
            if existing_record:
                # 存在则更新数据
                cursor.execute(update_query, (
                    cover_image, description, subtitle, is_free, price, 
                    ai_report_enabled, title
                ))
                print(f"更新数据: {title}")
            else:
                # 不存在则插入新数据
                cursor.execute(insert_query, (
                    title, cover_image, description, subtitle, is_free, 
                    price, ai_report_enabled
                ))
                print(f"插入新数据: {title}")
        
        connection.commit()
        print(f"成功处理 {cursor.rowcount} 条数据")
        
    except Error as e:
        print(f"处理数据时出错: {e}")
        connection.rollback()

def main():
    """主函数"""
    print("开始从API抓取数据...")
    
    # 从API获取数据
    data = fetch_data_from_api()
    if not data:
        print("获取数据失败")
        return
    
    # 打印获取到的数据（可选）
    
    print("数据获取成功，开始连接数据库...")
    
    # 连接数据库
    connection = create_mysql_connection()
    if not connection:
        print("数据库连接失败")
        return
    
    try:
        
        # 保存数据
        save_data_to_mysql(connection, data)
        
    finally:
        if connection.is_connected():
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    main()